Migrating or Upsizing Your Microsoft Access Database and ASP Code to SQL Server

There are two major procedures involved:
Upsizing or migrating the database and its contents from Access MDB file to SQL Server
Updating your ASP Code to reflect syntax differences between Access and SQL

Upsizing or Migrating the Database Structure and Contents

1. Make sure you have the SQL server info you need from your ISP
This will include the user name and password for the SQL server, and the name or IP address of the SQL server machine (e.g. sql.provider.com) - the SQL server may or may not be the same machine as your domain is hosted on.

2. Install the Microsoft Upsizing Wizard for Access
This is available for free download from Microsoft here. Download it to your hard drive and then run it. The install only takes a few seconds if your machine is quick.

3. Create a DSN for the SQL Server
This involves going to Start -> Settings -> Control Panel in Windows and going into your ODBC settings. Rather than repeat the instructions just check out the details in the readme.txt and help files that come with the Upsizing Wizard.

4. Upsize it baby!
Run Access (I think it has to be Access 97, I believe that's what the Upsizing Wizard works with and that's what I used.) Open the MDB file you want to upsize. Then go to Tools -> Addins and choose Upsize to SQL-Server.

Changing your ASP Code to reflect differences between Access and SQL

5. Change your connection string
Hopefully you have your connection string in an include file so you only have to change it in one place. If not, you'll have to change every occurrence. Your original connection string probably looked something like this:
dbName.ConnectionString = _
"DBQ=\path\filename.mdb;DefaultDir=;Driver={Microsoft Access Driver (*.mdb)};"&_
"DriverId=25;FIL=MS Access;ImplicitCommitSync=Yes;MaxBufferSize=512;MaxScanRows=8;"&_
"PageTimeout=5;SafeTransactions=0;Threads=3;UserCommitSync=Yes;;"

You will need to change it to something like this (there are more items possible in the string but this works for me):
dbName.ConnectionString = _
"Driver={SQL Server};DSN=dsnName;UID=sqlUserID;PWD=sqlPassword;"&_
"DB=dbName;Database=dbName;SERVER=sql.servername.com;"

6. Fix your SELECT statements
A few things I discovered that I had to change in my SELECT statements when I'm getting recordsets from the database:

• change fieldName=true to fieldName=1 (this is the number one not a lower-case L) -
  for example if your SELECT statement was "SELECT * FROM tblName WHERE tblName.ShowOnSite=true;" you would change it to "SELECT * FROM tblName WHERE tblName.ShowOnSite=1;"
• Where Access uses date() to represent today's date, SQL uses getdate(), so change date() to getDate() -
  for example change "SELECT * FROM tblName WHERE tblName.ExpiresDate > date() ;" to "SELECT * FROM tblName WHERE tblName.ExpiresDate > getdate() ;"

7. Fix DELETE statements if necessary
One of the pages I had was crashing on this statement: "DELETE * FROM tblName WHERE..." It turns out that while Access allows this syntax, SQL returns a syntax error because of the asterisk (*) so you'll have to change this type of DELETE statement to: "DELETE FROM tblName WHERE..." (no * in there at all)

8. Fix/change Cursor Types where necessary
One of my pages was crashing with this error:
"Multiple-step OLE DB operation generated errors"
By commenting out sections of the code I determined that it was crashing on display of a Memo field, all the other fields displayed fine. On some sort of divine inspiration I tried changing the CursorType setting: originally it was:
rsName.CursorType = 0
Changing it to 1 or 3 got rid of the OLD DB error. Don't ask me why. More info on CursorTypes can be found here if you want to learn more.

If you have any suggestions or questions feel free to email me! tech@spamblocktenvolt.com (remove the spamblock)